rm(list = ls())

setwd('/path/to/replication/')

library(data.table)
library(ggplot2)
library(xtable)

# Table B2: Hate Crime Before and After Covid-19

load('./data/panel_month_dummies.RData')

outcomes <- panel[, .(lapply(.SD, mean, na.rm=TRUE), lapply(.SD, sd, na.rm=TRUE)), by=covid2,
                  .SDcols=c('hc_pc', 'hc_pc_asians', 'hc_pc_african')]
var_outcomes <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african')
outcomes <- cbind(outcomes, rep(var_outcomes,2))

names(outcomes) <- c('group', 'mean', 'sd', 'var')
outcomes$sd <-  unlist(outcomes$sd)
outcomes$mean <-  unlist(outcomes$mean)
outcomes <- dcast(outcomes[,.(sd, var, group, mean)], var~group, value.var=c('sd', 'mean'))

setcolorder(outcomes, c('var', 'mean_0', 'sd_0', 'mean_1', 'sd_1'))

# order rows
vars <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african')
outcomes <- outcomes[vars, on="var"]

p_value <- c()
difference <- c()
for (i in vars) {
  vartest <- var.test(get(i) ~ covid2, data = panel)
  if (vartest$p.value > 0.1) {
    ttest <- t.test(get(i) ~ covid2, data = panel, var.equal = TRUE)
    
  } else {
    ttest <- t.test(get(i) ~ covid2, data = panel)
  }
  p_value <- c(p_value, ttest$p.value)
  difference <- c(difference, ttest$estimate[2]-ttest$estimate[1])
}


outcomes <- cbind(outcomes, difference, p_value)
outcomes <- outcomes[,lapply(.SD, as.numeric),by=var]
var <- c('All hate crimes', 'Asian hate crimes', 'African hate crimes')
outcomes <- cbind(var, outcomes[,var:=NULL])
summary_table <- xtable(outcomes,
                                caption="Hate Crimes Before and After Covid-19",
                                caption.above = TRUE,
                                digits=c(0,0,4,2,4,2,4,3),
                                align='llcccccc')

print(summary_table,include.rownames=FALSE, file = './tableB2.tex')

# Table B3: Descriptive Statistics by Covid-19 Related Death
load('./data/panel_month_dummies.RData')

panel[,foreign_pop_prop:=(foreign_pop/population)*100]
panel[,less_college:=(100-educ_diplBA)]
panel[,vote_extreme_right:=vs_extr_right*100]
dsummary_stats <- panel[!is.na(excess_deaths_jan_d)]
summary_stats <- dsummary_stats[month=='2011-01-01', .(lapply(.SD, mean, na.rm=TRUE), lapply(.SD, sd, na.rm=TRUE)), by=excess_deaths_jan_d,
                                .SDcols=c('population', 'above75_share', 'less_college', 'emp_unskilled',
                                          'unemp', 'unemp_male', 'unemp_young',
                                          'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')]
covars <- c('population', 'above75_share', 'less_college', 'emp_unskilled',
            'unemp', 'unemp_male', 'unemp_young',
            'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')
summary_stats <- cbind(summary_stats, rep(covars,2))
outcomes <-dsummary_stats[covid2==0, .(lapply(.SD, mean, na.rm=TRUE), lapply(.SD, sd, na.rm=TRUE)), by=excess_deaths_jan_d,
                          .SDcols=c('hc_pc', 'hc_pc_asians', 'hc_pc_african')]
var_outcomes <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african')
outcomes <- cbind(outcomes, rep(var_outcomes,2))

summary_stats <- rbind(outcomes, summary_stats)
names(summary_stats) <- c('group', 'mean', 'sd', 'var')
summary_stats$sd <-  unlist(summary_stats$sd)
summary_stats$mean <-  unlist(summary_stats$mean)
summary_stats <- dcast(summary_stats[,.(sd, var, group, mean)], var~group, value.var=c('sd', 'mean'))

setcolorder(summary_stats, c('var', 'mean_0', 'sd_0', 'mean_1', 'sd_1'))

# order rows
vars <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african',
          'population','above75_share', 'less_college', 'emp_unskilled',
          'unemp', 'unemp_male', 'unemp_young',
          'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')
summary_stats <- summary_stats[vars, on="var"]

p_value <- c()
difference <- c()
for (i in vars) {
  vartest <- var.test(get(i) ~ excess_deaths_jan_d, data = dsummary_stats)
  if (vartest$p.value > 0.1) {
    ttest <- t.test(get(i) ~ excess_deaths_jan_d, data = dsummary_stats, var.equal = TRUE)
    
  } else {
    ttest <- t.test(get(i) ~ excess_deaths_jan_d, data = dsummary_stats)
  }
  p_value <- c(p_value, ttest$p.value)
  difference <- c(difference, ttest$estimate[2]-ttest$estimate[1])
}


summary_stats <- cbind(summary_stats, difference, p_value)
summary_stats <- summary_stats[,lapply(.SD, as.numeric),by=var]
var <- c('All hate crimes', 'Asian hate crimes', 'African hate crimes',
         'Population', '% 65+', '% Foreign population', '% Less college',
         '% Unskilled labor', 'Unemployment rate', '% Male unemployed',
         '% Young unemployed', 'Vote share extreme right', 'Far-right mayor')
summary_stats <- cbind(var, summary_stats[,var:=NULL])
summary_table <- xtable(summary_stats,
                        caption="Descriptive Statistics by Covid-19 Related Deaths",
                        digits=c(0,0,4,2,4,2,4,3),
                        caption.above = TRUE,
                        align='llcccccc')

print(summary_table,include.rownames=FALSE, file = './tableB3.tex')


# Table B4: Descriptive Statistics by Covid-19 Related Unemployment
load('./data/panel_month_dummies.RData')
panel[,foreign_pop_prop:=(foreign_pop/population)*100]
panel[,less_college:=(100-educ_diplBA)]
panel[,vote_extreme_right:=vs_extr_right*100]

summary_stats <- panel[month=='2011-01-01', .(lapply(.SD, mean, na.rm=TRUE), lapply(.SD, sd, na.rm=TRUE)), by=losing_income_d,
                       .SDcols=c('population', 'less_college', 'emp_unskilled',
                                 'unemp', 'unemp_male', 'unemp_young',
                                 'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')]
covars <- c('population', 'less_college', 'emp_unskilled',
            'unemp', 'unemp_male', 'unemp_young',
            'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')
summary_stats <- cbind(summary_stats, rep(covars,2))
outcomes <- panel[covid2==0, .(lapply(.SD, mean, na.rm=TRUE), lapply(.SD, sd, na.rm=TRUE)), by=losing_income_d,
                  .SDcols=c('hc_pc', 'hc_pc_asians', 'hc_pc_african')]
var_outcomes <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african')
outcomes <- cbind(outcomes, rep(var_outcomes,2))

summary_stats <- rbind(outcomes, summary_stats)
names(summary_stats) <- c('group', 'mean', 'sd', 'var')
summary_stats$sd <-  unlist(summary_stats$sd)
summary_stats$mean <-  unlist(summary_stats$mean)
summary_stats <- dcast(summary_stats[,.(sd, var, group, mean)], var~group, value.var=c('sd', 'mean'))

setcolorder(summary_stats, c('var', 'mean_0', 'sd_0', 'mean_1', 'sd_1'))

# order rows
vars <- c('hc_pc', 'hc_pc_asians', 'hc_pc_african',
          'population', 'less_college', 'emp_unskilled',
          'unemp', 'unemp_male', 'unemp_young',
          'foreign_pop_prop', 'vote_extreme_right', 'extr_right_mayor')
summary_stats <- summary_stats[vars, on="var"]

p_value <- c()
difference <- c()
for (i in vars) {
  vartest <- var.test(get(i) ~ losing_income_d, data = panel)
  if (vartest$p.value > 0.1) {
    ttest <- t.test(get(i) ~ losing_income_d, data = panel, var.equal = TRUE)
    
  } else {
    ttest <- t.test(get(i) ~ losing_income_d, data = panel)
  }
  p_value <- c(p_value, ttest$p.value)
  difference <- c(difference, ttest$estimate[2]-ttest$estimate[1])
}


summary_stats <- cbind(summary_stats, difference, p_value)
summary_stats <- summary_stats[,lapply(.SD, as.numeric),by=var]
var <- c('All hate crimes', 'Asian hate crimes', 'African hate crimes',
         'Population', '% Foreign population', '% Less college',
         '% Unskilled labor', 'Unemployment rate', '% Male unemployed',
         '% Young unemployed', 'Vote share extreme right', 'Far-right mayor')
summary_stats <- cbind(var, summary_stats[,var:=NULL])
summary_table <- xtable(summary_stats,
                                caption="Descriptive Statistics by Covid-19 Related Unemployment",
                                digits=c(0,0,4,2,4,2,4,3),
                                caption.above = TRUE,
                                align='llcccccc')

print(summary_table,include.rownames=FALSE, file = './tableB4.tex')
